package com.hlw.hr_man.daoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.hlw.hr_man.Model.EmployeeModel;
import com.hlw.hr_man.dao.FindCardDao;
import com.hlw.hr_man.util.JDBCUtil;

/**
 * 查询名片夹Dao层实现类
 * @author qiyu
 *
 */
public class FindCardDaoImpl implements FindCardDao{

	@Override
	public int getEmpsCount(String searchName) {
		int ecount = 0;
		StringBuffer sql = new StringBuffer("select count(e_id) ecount from hr_employee where e_name like ?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement   pst =  con.prepareStatement(sql.toString());
			pst.setString(1, "%"+searchName+"%");
			ResultSet  rs = pst.executeQuery();
			if(rs.next()) {
				ecount = rs.getInt("ecount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ecount;
	}

	@Override
	public List<EmployeeModel> getEmps(int start,
			int pageSize, String searchName) {
		List<EmployeeModel>  Employee = new ArrayList<EmployeeModel>();
		Connection   con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("SELECT e_name,e_age,e_sex,e_id,dept_name ");
		sql.append("FROM hr_employee,hr_department WHERE e_name like ? and hr_employee.dept_id=hr_department.dept_id ");
		sql.append(" limit ?,?");
		try {
			PreparedStatement   pst =  con.prepareStatement(sql.toString());
			pst.setString(1, "%"+searchName+"%");
			pst.setInt(2, start);
			pst.setInt(3, pageSize);
			ResultSet  rs = pst.executeQuery();
			while(rs.next()) {
				EmployeeModel  s = new EmployeeModel();
				s.setE_id(rs.getInt("e_id"));
				s.setE_name(rs.getString("e_name"));
				s.setE_age(rs.getInt("e_age"));
				s.setE_sex(rs.getString("e_sex"));
				s.setDept_name(rs.getString("dept_name"));
				Employee.add(s);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return Employee;
	}
	@Override
	public List<EmployeeModel> getEmps(int e_id, int start,
			int pageSize) {
		List<EmployeeModel>  Employee = new ArrayList<EmployeeModel>();
		Connection   con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select e_name,e_age,e_sex,e_id,dept_name");
		sql.append(" from hr_employee,hr_department where e_id = ? and hr_employee.dept_id=hr_department.dept_id ");
		sql.append(" limit ?,?");
		try {
			PreparedStatement   pst =  con.prepareStatement(sql.toString());
			pst.setInt(1, e_id);
			pst.setInt(2, start);
			pst.setInt(3, pageSize);
			ResultSet  rs = pst.executeQuery();
			while(rs.next()) {
				EmployeeModel  s = new EmployeeModel();
				s.setE_id(rs.getInt("e_id"));
				s.setE_name(rs.getString("e_name"));
				s.setE_age(rs.getInt("e_age"));
				s.setE_sex(rs.getString("e_sex"));
				s.setDept_name(rs.getString("dept_name"));
				Employee.add(s);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return Employee;
	}


	@Override
	public int getCardsCount(int id) {
		int ecount = 0;
		StringBuffer sql = new StringBuffer("select count(e_id) ecount from hr_cardcase where hr_cardcase.e_id like ?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement   pst =  con.prepareStatement(sql.toString());
			pst.setInt(1, id);
			ResultSet  rs = pst.executeQuery();
			if(rs.next()) {
				ecount = rs.getInt("ecount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ecount;
	}

	@Override
	public EmployeeModel getCardCase(int id) {
		EmployeeModel  s = new EmployeeModel();
		Connection   con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select e_name,e_age,e_sex,card_company,card_address,card_tel,card_email,hr_cardcase.card_id,dept_name ");
		sql.append(" FROM hr_employee,hr_cardcase,hr_department WHERE hr_employee.e_id=hr_cardcase.e_id and hr_employee.e_id = ? and hr_employee.dept_id=hr_department.dept_id");
		try {
			PreparedStatement   pst =  con.prepareStatement(sql.toString());
			pst.setInt(1, id);
			ResultSet  rs = pst.executeQuery();
			while(rs.next()) {
				s.setCard_id(rs.getInt("card_id"));
				s.setE_name(rs.getString("e_name"));
				s.setE_age(rs.getInt("e_age"));
				s.setE_sex(rs.getString("e_sex"));
				s.setCard_address(rs.getString("card_address"));
				s.setCard_company(rs.getString("card_company"));
				s.setCard_email(rs.getString("card_email"));
				s.setCard_tel(rs.getString("card_tel"));
				s.setDept_name(rs.getString("dept_name"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return s;
	}
	@Override
	public int deleteCard(int e_id) {
		// TODO Auto-generated method stub
		int flag = 0;
		Connection   con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("delete from hr_cardcase where e_id=?");
		try {
			PreparedStatement   pst =  con.prepareStatement(sql.toString());
			pst.setInt(1, e_id);
			int  i  = pst.executeUpdate();
			if(i>0){
				flag=1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
	@Override
	public int addCard(EmployeeModel e) {
		// TODO Auto-generated method stub
		int flag = 0;
		Connection con=null;
		con=JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("insert into hr_cardcase(card_company,card_address,card_tel,card_email,e_id) values(?,?,?,?,?);");		
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setString(1, e.getCard_company());
			pst.setString(2, e.getCard_address());
			pst.setString(3, e.getCard_tel());
			pst.setString(4, e.getCard_email());
			pst.setInt(5, e.getE_id());
			if(pst.executeUpdate()>0){
				flag=1;
			}
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		return flag;
	}
	@Override
	public int updateCard(EmployeeModel e) {
		// TODO Auto-generated method stub
		int flag = 0;
		Connection con=null;
		con=JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("update hr_cardcase set card_company=?,card_address=?,card_tel=?,card_email=? where e_id =?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, e.getCard_company());
			pst.setString(2, e.getCard_address());
			pst.setString(3, e.getCard_tel());
			pst.setString(4, e.getCard_email());
			pst.setInt(5, e.getE_id());
			int i = pst.executeUpdate();
			if(i>0){
				flag = 1;
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		return flag;
	}
	@Override
	public boolean findCardByE_id(int e_id) {
		// TODO Auto-generated method stub
		boolean flag = false;
		Connection con=null;
		con=JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select * from hr_cardcase WHERE e_id = ?");
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setInt(1, e_id);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				flag = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public int findEmpsCount(String searchtype) {
		int ecount = 0;
		StringBuffer sql = new StringBuffer("select count(e_id) as ecount from hr_employee,hr_department where hr_department.dept_id=hr_employee.dept_id and dept_name like ?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement   pst =  con.prepareStatement(sql.toString());
			pst.setString(1, "%"+searchtype+"%");
			ResultSet  rs = pst.executeQuery();
			if(rs.next()) {
				ecount = rs.getInt("ecount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ecount;
	}

	@Override
	public List<EmployeeModel> findEmps(int start, int pageSize,
			String searchtpye) {
		// TODO Auto-generated method stub
		List<EmployeeModel>  Employee = new ArrayList<EmployeeModel>();
		Connection   con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("SELECT e_name,e_age,e_sex,e_id,dept_name ");
		sql.append("FROM hr_employee,hr_department WHERE dept_name like ? and hr_employee.dept_id=hr_department.dept_id ");
		sql.append(" limit ?,?");
		try {
			PreparedStatement   pst =  con.prepareStatement(sql.toString());
			pst.setString(1, "%"+searchtpye+"%");
			pst.setInt(2, start);
			pst.setInt(3, pageSize);
			ResultSet  rs = pst.executeQuery();
			while(rs.next()) {
				EmployeeModel  s = new EmployeeModel();
				s.setE_id(rs.getInt("e_id"));
				s.setE_name(rs.getString("e_name"));
				s.setE_age(rs.getInt("e_age"));
				s.setE_sex(rs.getString("e_sex"));
				s.setDept_name(rs.getString("dept_name"));
				Employee.add(s);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return Employee;
	}

}
